package dao;


import pojo.entity.User;
import utils.DbUtil;

import java.sql.*;

import static management.UserManagement.USERNAME;

public interface UserDao {
    //增加用户
    public static void addUser(User user) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql
        String sql = "INSERT INTO user(name, username, password,money,status,online,last_action_time)"
                + "values(" + "?,?,?,?,?,?,?)";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, user.getName());
        ptmt.setString(2, user.getUsername());
        ptmt.setString(3, user.getPassword());
        ptmt.setDouble(4, user.getMoney());
        ptmt.setInt(5, user.getStatus());
        ptmt.setInt(6, user.getOnline());
        ptmt.setTimestamp(7, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));

        //执行
        ptmt.execute();
    }

    //修改用户
    public static void updateUser(User user) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "UPDATE user" +
                " set name=?, username=?, password=?,money=?,status=?,online = ?,last_action_time=?"+
                " where id=?";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setString(1, user.getName());
        ptmt.setString(2, user.getUsername());
        ptmt.setString(3, user.getPassword());
        ptmt.setDouble(4, user.getMoney());
        ptmt.setInt(5, user.getStatus());
        ptmt.setInt(6, user.getOnline());
        ptmt.setTimestamp(7, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
        ptmt.setInt(8, user.getId());

        //执行
        ptmt.execute();
    }


    //删除用户
    public static void delUser(Integer id) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "delete from user where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);

        //传参
        ptmt.setInt(1, id);

        //执行
        ptmt.execute();
    }

    //根据id查询用户
    public static User getUserById(Integer id) throws SQLException {
        User user = null;
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "select * from  user where id=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setInt(1, id);
        //执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setMoney(rs.getDouble("money"));
            user.setStatus(rs.getInt("status"));
            user.setOnline(rs.getInt("online"));
            user.setLastActionTime(rs.getTimestamp("last_action_time").toLocalDateTime());
        }
        return user;
    }

    //根据用户名查询用户信息
    public static User getUserByUsername(String username) throws SQLException {
        User user = null;
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "select * from  user where username=?";
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1, username);
        //执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setMoney(rs.getDouble("money"));
            user.setStatus(rs.getInt("status"));
            user.setOnline(rs.getInt("online"));
            user.setLastActionTime(rs.getTimestamp("last_action_time").toLocalDateTime());
        }
        return user;
    }

    //根据用户名设置管理员是否在线
    public static void setUserOnlineStatus(String username, Integer online) throws SQLException {
        //获取连接
        Connection conn = DbUtil.getConnection();
        //sql, 每行加空格
        String sql = "UPDATE user" +
                " set online = ?"+
                " where username =?";
        //预编译
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

        //传参
        ptmt.setInt(1, online);
        ptmt.setString(2, username);
        //执行
        ptmt.execute();
    }
    public static boolean isUserOfflineInDB() throws SQLException {
        String sql = "SELECT online FROM user WHERE username=?";
        Connection c = DbUtil.getConnection();
        PreparedStatement ps = c.prepareStatement(sql);
        ps.setString(1, USERNAME);
        ResultSet rs = ps.executeQuery();
        return rs.next() && rs.getInt(1) == 0;   // 0=离线
    }

}
